<!DOCTYPE html>
<html lang="">

<head>
	<meta name="generator" content="Hugo 0.73.0" />
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<meta name="author" content="Ap Chen ">
<meta name="description" content="数据库设计概述 数据库设计：根据用户需求(数据需求，处理需求)和平台约束(硬件，操作系统和DBMS)设计数据模式 数据库设计的生命周期 需求分析（" />
<meta name="keywords" content="blog" />
<meta name="robots" content="noodp" />

<link rel="canonical" href="https://fziks.gitee.io/notes/database/9-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1/" />

<meta itemprop="name" content="9-数据库设计">
<meta itemprop="description" content="数据库设计概述 数据库设计：根据用户需求(数据需求，处理需求)和平台约束(硬件，操作系统和DBMS)设计数据模式 数据库设计的生命周期 需求分析（">
<meta itemprop="datePublished" content="2019-05-26T00:00:00&#43;00:00" />
<meta itemprop="dateModified" content="2019-05-26T00:00:00&#43;00:00" />
<meta itemprop="wordCount" content="810">



<meta itemprop="keywords" content="" />
<meta property="og:title" content="9-数据库设计" />
<meta property="og:description" content="数据库设计概述 数据库设计：根据用户需求(数据需求，处理需求)和平台约束(硬件，操作系统和DBMS)设计数据模式 数据库设计的生命周期 需求分析（" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://fziks.gitee.io/notes/database/9-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1/" />
<meta property="article:published_time" content="2019-05-26T00:00:00+00:00" />
<meta property="article:modified_time" content="2019-05-26T00:00:00+00:00" />

<meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="9-数据库设计"/>
<meta name="twitter:description" content="数据库设计概述 数据库设计：根据用户需求(数据需求，处理需求)和平台约束(硬件，操作系统和DBMS)设计数据模式 数据库设计的生命周期 需求分析（"/>


<link rel="apple-touch-icon" sizes="60x60" href="https://fziks.gitee.io/icons/apple-touch-icon.png">
<link rel="icon" type="image/png" sizes="32x32" href="https://fziks.gitee.io/icons/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="16x16" href="https://fziks.gitee.io/icons/favicon-16x16.png">
<link rel="manifest" href="https://fziks.gitee.io/icons/site.webmanifest">
<link rel="mask-icon" href="https://fziks.gitee.io/icons/safari-pinned-tab.svg" color="#5bbad5">
<link rel="shortcut icon" href="https://fziks.gitee.io/icons/favicon.ico">
<meta name="msapplication-TileColor" content="#ffffff">
<meta name="msapplication-config" content="/icons/browserconfig.xml">
<meta name="theme-color" content="#ffffff">

<title>9-数据库设计</title>


<link rel="stylesheet" href="//at.alicdn.com/t/font_1559566_wk214kwa2dn.css">


    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.css" integrity="sha384-zB1R0rpPzHqg7Kpt0Aljp8JPLqbXI3bhnPWROx27a9N0Ll6ZP/+DiW/UqRcLbRjq" crossorigin="anonymous">



    
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/8.0.1/normalize.css" integrity="sha256-WAgYcAck1C1/zEl5sBl5cfyhxtLgKGdpI3oKyJffVRI=" crossorigin="anonymous" />
    
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.7.2/animate.css" integrity="sha256-a2tobsqlbgLsWs7ZVUGgP5IvWZsx8bTNQpzsqCSm5mk=" crossorigin="anonymous" />
    
   <link href="https://stackpath.bootstrapcdn.com/bootswatch/4.4.1/materia/bootstrap.min.css" rel="stylesheet" integrity="sha384-1tymk6x9Y5K+OF0tlmG2fDRcn67QGzBkiM3IgtJ3VrtGrIi5ryhHjKjeeS60f1FA" crossorigin="anonymous">
    
    
    <link rel="stylesheet" href="https://fziks.gitee.io/sass/main_cdn.min.270b43bb8631af4497ed45b90db42c517e86c9511418de9152f134d02ed32b87.min.2192baea245cf318085511589e62bfbdb3fbe4fb0eef718f1be9af91c10542ce.css" integity="sha256-IZK66iRc8xgIVRFYnmK/vbP75PsO73GPG&#43;mvkcEFQs4=">

</head>

<body style="overflow-x: unset;">
	<div class="container-fluid">
		<div class="row d-print-block">
			<div class="col-12 col-md-3 col-lg-2 bd-sidebar d-print-none">
				<div class="d-flex mt-3 border-bottom">
        <span class="navbar-brand w-100" style="display: grid;">
            <small>
                <a href="https://fziks.gitee.io/" class="text-black-50">
                    <i class="iconfont icon-back-arrow-"></i>
                </a>
                Ap Chen's
            </small>
            <a class="text-dark" href="https://fziks.gitee.io/notes/">
                Notes
            </a>
        </span>
        <button class="btn btn-link text-dark d-md-none p-0 ml-3" type="button" data-toggle="collapse"
            data-target="#bd-docs-nav" aria-controls="bd-docs-nav" aria-expanded="true"
            aria-label="Toggle docs navigation">
            <i class="fad fa-bars"></i>
        </button>
    </div>
				<nav id="bd-docs-nav" class="collapse bd-links">
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/computer-network/">
            
            
                <span class="icontext">S</span>
            
            
            计算机网络
        </a>
    </div>
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/operating-system/">
            
            
                <span class="icontext">S</span>
            
            
            操作系统
        </a>
    </div>
    
    
    
    <div class="bd-toc-item active bg-light">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/database/">
            <i class="iconfont icon-back-arrow-reverse"></i>
            数据库
        </a>
        <ul class="nav bd-sidenav">
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/1-%E6%A6%82%E8%BF%B0/">1-概述</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/2-%E6%95%B0%E6%8D%AE%E6%A8%A1%E5%9E%8B/">2-数据模型</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/3-%E5%85%B3%E7%B3%BB%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F/">3-关系数据库系统</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/8-%E8%A7%84%E8%8C%83%E5%8C%96%E7%90%86%E8%AE%BA/">8-规范化理论</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/5-%E5%AE%89%E5%85%A8%E6%80%A7%E4%B8%8E%E5%AE%8C%E6%95%B4%E6%80%A7/">5-安全性与完整性</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/6-%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86/">6-事务处理</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/7-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E7%89%A9%E7%90%86%E7%BB%84%E7%BB%87/">7-数据库的物理组织</a>
            </li>
            
            
            
            <li>
                <a href="https://fziks.gitee.io/notes/database/8-%E6%95%B0%E6%8D%AE%E4%BA%A4%E6%8D%A2/">8-数据交换</a>
            </li>
            
            
            
            <li class="active">
                <a href="https://fziks.gitee.io/notes/database/9-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1/">
                    <i class="fad fa-chevron-right mr-1"></i>
                    9-数据库设计
                </a>
            </li>
            
            
        </ul>
    </div>
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/cs50-introduction-to-computer-science/">
            
            
                <span class="icontext">S</span>
            
            
            CS50 MIT
        </a>
    </div>
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/advanced-programming/">
            
            
                <span class="icontext">A</span>
            
            
            高级程序设计
        </a>
    </div>
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/deep-learning/">
            
            
                <span class="icontext">O</span>
            
            
            深度学习基础
        </a>
    </div>
    
    
    
    <div class="bd-toc-item">
        <a class="bd-toc-link" href="https://fziks.gitee.io/notes/%E6%96%87%E7%8C%AE%E7%AE%A1%E7%90%86%E4%B8%8E%E4%BF%A1%E6%81%AF%E5%88%86%E6%9E%90/">
            
            
                <span class="icontext">O</span>
            
            
            文献管理与信息分析
        </a>
    </div>
    
    
</nav>
			</div>
			<div class="col-12 col-md-9 col-lg-10 d-print-block">
				<div class="row d-print-block">
					<main class="col-12 col-md-10 col-lg-9 py-md-3 pl-md-5 bd-content d-print-block" role="main">
						<div id="title" class="my-4 border-bottom">
							<span>数据库</span>
							<h2>9-数据库设计</h2>
							<footer>
								<span>
									<i class="iconfont icon-NewFile mr-2"></i>
									2019-05-26 08:00 CST
								</span> <br />
								<span>
									<i class="iconfont icon-modify mr-2"></i>
									2019-05-26 08:00 CST
								</span> <br />
								<span>
									<i class="iconfont icon-copyright mr-2"></i>
									CC BY-NC 4.0
								</span>
							</footer>
						</div>
						<div id="content" class="hl-h2">
							
							
							
							
							
							
							
							
							
							
							
							
							<h2 id="数据库设计概述">数据库设计概述<a href="#数据库设计概述" class="anchor" aria-hidden="true"><i class="iconfont icon-link"></i></a></h2>
<ul>
<li>数据库设计：根据用户需求(数据需求，处理需求)和平台约束(硬件，操作系统和DBMS)设计数据模式</li>
<li>数据库设计的生命周期
<ul>
<li>需求分析（需求分析说明书）</li>
<li>概念设计（概念数据模型）</li>
<li>逻辑设计（逻辑数据模型）</li>
<li>物理设计（数据库内模式）</li>
<li>编码测试运行，迭代</li>
</ul>
</li>
</ul>
<h2 id="需求分析">需求分析<a href="#需求分析" class="anchor" aria-hidden="true"><i class="iconfont icon-link"></i></a></h2>
<ul>
<li>Things</li>
<li>Attributes of Things</li>
<li>Relationship among Things</li>
</ul>
<h2 id="概念设计">概念设计<a href="#概念设计" class="anchor" aria-hidden="true"><i class="iconfont icon-link"></i></a></h2>
<ul>
<li>工具：E-R模型，EE-R模型，面向对象模型</li>
<li>设计过程
<ul>
<li>用户分解：将用户分为功能相对独立的若干组</li>
<li>视图设计：针对每个用户设计其数据视图</li>
<li>视图集成
<ul>
<li>等同：多个数据对象具有相同语义</li>
<li>聚合：数据对象间的一种组成关系</li>
<li>抽取：不同实体中的相同属性提取成一个新的实体，并构成具有继承关系的结构</li>
</ul>
</li>
<li>视图修改
<ul>
<li>命名冲突</li>
<li>概念冲突</li>
<li>域冲突</li>
<li>约束冲突</li>
</ul>
</li>
</ul>
</li>
</ul>
<h2 id="逻辑设计">逻辑设计<a href="#逻辑设计" class="anchor" aria-hidden="true"><i class="iconfont icon-link"></i></a></h2>
<p>将 EE-R 转换为 DBMS 支持的逻辑数据模型（转换为关系数据库模式）</p>
<ul>
<li>命名与属性域的处理
<ul>
<li>在同一个数据库模式中，关系名具有唯一性</li>
<li>在同一个关系模式中，属性名具有唯一性</li>
<li>根据DBMS的选型进行必要的数据类型转换</li>
</ul>
</li>
<li>非原子属性处理
<ul>
<li>集合属性：原有关系的一个元组将被纵向展开成多个元组</li>
<li>元组属性：将一个元组属性横向展开成多个属性</li>
</ul>
</li>
<li>联系的特殊转换
<ul>
<li>一个联系可以被转换成一个关系</li>
<li>联系也可被归并到相关联的实体所对应的关系模式中去</li>
<li>根据 1:1,1:n,m:n 转换为 1/2/3 个关系</li>
</ul>
</li>
<li>继承的转换
<ul>
<li>每个实体集都转换为一个关系</li>
<li>只有最底层的叶子结点才会被转换为关系，并从其所有超实体集中继承属性</li>
<li>被转换为单个关系，其中含有所有实体集中的属性。</li>
</ul>
</li>
<li>规范化：至少满足 3NF</li>
<li>RDBMS 性能调整
<ul>
<li>逆规范化：减少连接运算次数</li>
<li>关系的分割
<ul>
<li>水平分割：将一个关系的元组集合划分为若干个不相交的子集，每个子集对应一个子关系模式</li>
<li>将一个关系模式纵向分解成若干个子关系模式</li>
</ul>
</li>
<li>尽量使用快照</li>
</ul>
</li>
<li>约束条件设置</li>
</ul>
<h2 id="物理设计">物理设计<a href="#物理设计" class="anchor" aria-hidden="true"><i class="iconfont icon-link"></i></a></h2>
<ul>
<li>存储方式的设计
<ul>
<li>索引
<ul>
<li>在主关键字和外关键字上建立索引</li>
<li>以读为主的关系应尽可能多地建立索引</li>
</ul>
</li>
<li>集簇
<ul>
<li>将有关的数据元组集中存放于一个或相邻的物理块内或同一柱面内以提高查询效率</li>
</ul>
</li>
<li>HASH</li>
</ul>
</li>
<li>存储结构的设计
<ul>
<li>分区设计：数据存放位置的设计
<ul>
<li>减少访盘冲突，提高 I/O 并行性</li>
<li>分散热点数据，均衡 I/O 负担</li>
</ul>
</li>
<li>系统参数配置</li>
</ul>
</li>
</ul>

						</div>
					</main>
					<div class="d-none d-lg-block col-lg-3 bd-toc d-print-none">
						<div class="btn-group-vertical w-100 my-3">
    
    <a class="btn btn-outline-secondary text-dark w-100 p-2" href="https://list.html" target="_blank">
        <i class="iconfont icon-LC_icon_list_line"></i><br />待更新列表
    </a>
    

    
    <a class="btn btn-outline-secondary text-dark w-100 p-2" href="mailto:littlegreedy@qq.com"
        target="_blank">
        <i class="iconfont icon-discussion"></i><br />纠错与咨询
    </a>
    
    
    
    
        <a class="btn btn-outline-secondary text-dark w-100 p-2" href="#" onclick="window.print()">
            <i class="iconfont icon-dayin"></i><br />打印本页
        </a>
    
    
</div>
						<h4 class="card-title pb-0">目录</h4>
						<nav id="TableOfContents">
  <ul>
    <li><a href="#数据库设计概述">数据库设计概述</a></li>
    <li><a href="#需求分析">需求分析</a></li>
    <li><a href="#概念设计">概念设计</a></li>
    <li><a href="#逻辑设计">逻辑设计</a></li>
    <li><a href="#物理设计">物理设计</a></li>
  </ul>
</nav>
						
						
						<div id="disqus_thread"></div>
							<script>
							

							

							(function() { 
							var d = document, s = d.createElement('script');
							s.src = "https://"+"your site name on disqus"+".disqus.com/embed.js";
							s.setAttribute('data-timestamp', +new Date());
							(d.head || d.body).appendChild(s);
							})();
							</script>
							<noscript>Please enable JavaScript to view the <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>                  
					  	</div>
						
					</div>
				</div>
			</div>
		</div>
	</div>

	<script
    src="https://code.jquery.com/jquery-3.4.1.min.js"
    integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="
    crossorigin="anonymous"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script><script defer src="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.js" integrity="sha384-y23I5Q6l+B6vatafAwxRu/0oK/79VlbSz7Q9aiSZUvyWYIYsd+qj+o24G5ZU2zJz" crossorigin="anonymous"></script>
<script defer src="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/contrib/auto-render.min.js" integrity="sha384-kWPLUVMOks5AQFrykwIup5lo0m3iMkkHrD0uJ4H5cjeGihAutqP0yW0J6dpFiVkI" crossorigin="anonymous"
onload="renderMathInElement(document.body);"></script>

<script type="text/javascript" src="https://fziks.gitee.io/custom.min.d3e1b7647f32dbe7e0140398739a26dad3f3470fc1eebe0741ef33668f1b7bd0b2917dc6efb9f0d9f1092b91dca502cab1b883863f02530133a8a8ef609926af.js" integrity="sha512-0&#43;G3ZH8y2&#43;fgFAOYc5om2tPzRw/B7r4HQe8zZo8be9CykX3G77nw2fEJK5HcpQLKsbiDhj8CUwEzqKjvYJkmrw=="></script>
<script type="text/javascript">

document.addEventListener("DOMContentLoaded", function () {
    renderMathInElement(
        document.body, {
            delimiters: [
                {
                    left: "$$",
                    right: "$$",
                    display: true
                },
                {
                    left: "\\[",
                    right: "\\]",
                    display: true
                },
                {
                    left: "$",
                    right: "$",
                    display: false
                },
                {
                    left: "\\(",
                    right: "\\)",
                    display: false
                }
            ],
            strict: false
        }
    );
});


$(document).on('click', 'a[href^="#"]', function (event) {
    event.preventDefault();

    $('html, body').animate({
        scrollTop: $($.attr(this, 'href')).offset().top
    }, 500);
});
</script>




</body>

</html>